package fr.afcepf.ai85.gepeto.service;

import java.util.ArrayList;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;

import fr.afcepf.ai85.businessObj.ResultatFacture;
import fr.afcepf.ai85.hibernate.util.HibernateUtil;

public class ServiceFactureHib
{

		public static void main(String[] args) {
	
			// periode 
			// ecole Fernand-Leger
			rechercheFactureByperiodeEcole(2, 10);
	
		}

	public static List<ResultatFacture> rechercheFactureByperiodeEcole(int idPeriode, int idEcole) {

		Session session = HibernateUtil.currentSession();
		Transaction tx = session.beginTransaction();

		Query sql1 = session
				.createSQLQuery("select nom_enfant, prenom_enfant, id_type_ecole, nom_ecole, id_facture, date_facture, date_reglement, montant_gobal "
						+ "from facture f, inscription_periscolaire i, eleve e, inscription_ecole ie, ecole ec "
						+ "where f.id_inscription=i.id_inscription "
						+ "and i.id_eleve=e.id_eleve "
						+ "and e.id_eleve=ie.id_eleve "
						+ "and ec.identifiant_ecole=ie.identifiant_ecole "
						+ "and i.id_periode = ? and ie.identifiant_ecole=? order by id_facture");
		sql1.setInteger(0, idPeriode);
		sql1.setInteger(1, idEcole);


		List<Object[]> liste = sql1.list();
		List<ResultatFacture> resFact = convertListFacture(liste);
		

		System.out.println("ServiceFactureHib : methode rechercheFactureByperiodeEcole()");

		for (Object[] objects : liste) {
			System.out.println(objects[0] + "\t" + objects[1] + "\t"
					+ objects[2] + "\t" + objects[3] + "\t" + objects[4] + "\t"
					+ objects[5] + "\t" + objects[6]+ "\t" + objects[7]);

		}

		HibernateUtil.closeSession();

		return resFact;
	}

	public static List<ResultatFacture> convertListFacture(List<Object[]> liste)
	{	
		 List<ResultatFacture> resFact = new ArrayList<ResultatFacture>();
		
		for(Object[] obj : liste)
		{
			ResultatFacture rf = new ResultatFacture();
			rf.nomEnf = obj[0].toString();
			rf.prenomEnf = obj[1].toString();
			rf.nomEcole = obj[3].toString();
			rf.idFacture = obj[4].toString();
			rf.dateFacture = obj[5].toString();
			
			if(obj[6] != null)
			{
				rf.dateReglement = obj[6].toString();
				rf.paye = true;
			} else
			{
				rf.paye = false;
			}
			
			rf.montantGlobal = obj[7].toString();

			resFact.add(rf);
		}

		return resFact;
	}


	
}
